# ========================================================================
# 顾及指标时空统一性计算指标权重和评价得分-县域数据（2005,2010,2015,2019）
# ========================================================================
import numpy as np
import pandas as pd

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
from get_proportion import get_proportion
from get_weight_by_entropy import get_weight_by_entropy
from get_weight_by_msd import get_weight_by_msd
from load_dataframe import load_dataframe
from normalizing import normalizing


def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')

# See PyCharm help at https://www.jetbrains.com/help/pycharm/
# 城市：昆明市，曲靖市，玉溪市，楚雄州，红河州
# 指标类型：新型城镇化，生态环境
zb_type = '生态环境'
zb_file = 'data/年鉴统计数据/县域数据/县域指标分类体系.xlsx'
# [2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]
years = [2005, 2010, 2015, 2019]
index = pd.Series(years)
scores_df = pd.DataFrame()  # 指标得分
# 加载指标
df_zb = pd.read_excel(zb_file, sheet_name=zb_type)
zb_names = np.array(df_zb.columns)
# 加载区县
df_city = pd.read_excel(zb_file, sheet_name='区县')
city_names = np.array(df_city.columns)
df_allcity = pd.DataFrame()
# 读取所有区县数据 滇中县域-独立时序-新型城镇化-4个年度.xlsx
excel_name = '滇中县域-独立时序-' + zb_type + '-4个年度.xlsx'
csh_file = 'data/年鉴统计数据/县域数据/最终成果/' + excel_name
for city_name in city_names:
    # 加载数据
    dataframe = pd.read_excel(csh_file, sheet_name=city_name, index_col=0)
    print('-----------原始数据-----------')
    print(dataframe)
    # 合并同类项
    df_allcity = df_allcity.append(dataframe, ignore_index=True)

print('=======================合并同类项=======================')
print(df_allcity)
with pd.ExcelWriter('滇中县域-'+zb_type + '-归一化处理-计算权重.xlsx') as writer:
    df_allcity.to_excel(writer, sheet_name="合并数据")
    # 归一化处理
    normal_df = normalizing(df_allcity)
    normal_df.to_excel(writer, sheet_name='归一化')
    # 求单个数值占整个序列数值之和的比重
    proportion_df = get_proportion(normal_df)
    # 计算指标权重
    df_weight = pd.DataFrame()
    # 熵权法确定指标权重
    weight_ent = get_weight_by_entropy(proportion_df)
    df_weight = df_weight.append(weight_ent)
    # 均方差决策法确定指标权重
    weight_msd = get_weight_by_msd(proportion_df)
    df_weight = df_weight.append(weight_msd)
    # 求综合权重
    weights = 0.5 * (weight_ent + weight_msd)
    weights.name = '综合权重'
    print('----------综合权重----------')
    print(weights)
    df_weight = df_weight.append(weights)
    print('----------权重----------')
    print(df_weight)
    df_weight_T = pd.DataFrame(df_weight.values.T, columns=df_weight.index, index=df_weight.columns)
    df_weight_T = df_weight_T.round(2)
    df_weight_T.to_excel(writer, sheet_name='权重')
    # 计算指标得分
    scores_df = pd.DataFrame(columns=normal_df.columns, index=normal_df.index)
    for i in weights.index:
        scores_df[i] = weights[i] * normal_df[i]
    # 计算指标综合得分
    scores_df['综合得分'] = scores_df.sum(axis=1)
    print('----------指标得分----------')
    print(scores_df)
    scores_df.to_excel(writer, sheet_name='指标得分')
df_cell = {}
for colname in scores_df.columns:
    df_cell[colname] = pd.DataFrame(index=index)
step = len(years)
with pd.ExcelWriter('滇中县域-' + zb_type + '-各指标评价得分-独立时序.xlsx') as writer2:
    for i in range(len(city_names)):
        name = city_names[i]
        start = i * step
        end = start + step
        df_city_score = scores_df.iloc[start:end]
        df_city_score2 = pd.DataFrame(df_city_score.values, index=index, columns=scores_df.columns)
        print(name + '----------指标得分----------')
        print(df_city_score2)
        df_city_score2.to_excel(writer2, sheet_name=name)
        for colname in df_city_score2.columns:
            df_cell[colname][name] = df_city_score2[colname]

with pd.ExcelWriter('滇中县域-' + zb_type + '-综合评价得分-统一时序.xlsx') as writer3:
    for cellname in df_cell:
        df_sub = df_cell[cellname]
        idx = cellname.rfind('(')
        sheetname = cellname
        if idx > 0:
            sheetname = cellname[0:idx]
        df_sub.to_excel(writer3, sheet_name=sheetname)
